/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Configurations;

import java.sql.*;
import java.util.TreeMap;
import java.util.Iterator;

/**
 * A singleton list of application implemented using treemap
 * key = first_name + last_name + email
 * value = application
 * @author mizheng
 */
public class ApplicationList {

    private static ApplicationList instance = null;
    private TreeMap<String, Application> ApplicationList;

    private ApplicationList() {
        ApplicationList = new TreeMap<String, Application>();
    }

    public static ApplicationList getInstance() {
        if (instance == null) {
            instance = new ApplicationList();
        }
        return instance;
    }

    public void add(String applicationID, Application application) {
        ApplicationList.put(applicationID, application);
    }

    public void remove(String applicationID) {
        ApplicationList.remove(applicationID);
    }

    public Application get(String applicationID) {
        return ApplicationList.get(applicationID);
    }

    public Iterator iterator() {
        return ApplicationList.values().iterator();
    }

    public void update() {
        mergeDataFromAY();
        mergeDataFromOST();
    }   

    /*
     * Get data from corresponding AY file    
     */
    private void mergeDataFromAY() {
        Connection con = null;
        ResultSet rs;

        try {
            Class.forName(MySQL.DB_DRIVER);
            con = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);
            
            //Select the data from the database for information contained in AY files 
            String sql = "select * from " + MySQL.DB_APPS ; 
            Statement s = con.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();

            while (rs.next()) {
                String email = rs.getString("email_address");
                Boolean isNewApplication = false;
                Application application = get(email);
                if (application == null) {
                    application = new Application(email);
                    isNewApplication = true;
                }
                
                String last_name = rs.getString("last_name");
                String first_name = rs.getString("first_name");

                application.setLast_name(last_name);
                application.setFirst_name(first_name);
                application.setMiddle_name(rs.getString("middle_name"));
                application.setResearch_area(rs.getString("research_area"));
                application.setUg_inst(rs.getString("ug_inst"));
                application.setUg_GPA(rs.getDouble("ug_GPA"));
                application.setGrad_inst(rs.getString("grad_inst"));
                application.setGrad_GPA(rs.getDouble("grad_GPA"));

                if (isNewApplication) {
                    add(first_name.concat(last_name.concat(email)), application);
                }
            }
            rs.close();
            s.close();
        } catch (Exception e) {
            System.out.println("Exception is ;" + e);
        }
    }

    /*
     * Get data from corresponding OST file    
     */
    private void mergeDataFromOST() {
        Connection con = null;
        ResultSet rs;

        try {
            Class.forName(MySQL.DB_DRIVER);
            con = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);

            //Select the data from the database for information contained in AY files 
            String sql = "select * from " + MySQL.DB_SCORES;
            Statement s = con.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();


            while (rs.next()) {
                String email = rs.getString("email_address");
                Application application = get(email);
                if (application != null) {
                    application.setOfcl_GRE_V(rs.getInt("ofcl_GRE_V"));
                    application.setOfcl_GRE_V_pctile(rs.getFloat("ofcl_GRE_V_pctile"));
                    application.setOfcl_GRE_Q(rs.getInt("ofcl_GRE_Q"));
                    application.setOfcl_GRE_Q_pctile(rs.getFloat("ofcl_GRE_Q_pctile"));
                    application.setOfcl_GRE_A(rs.getInt("ofcl_GRE_A"));
                    application.setOfcl_GRE_A_pctile(rs.getFloat("ofcl_GRE_A_pctile"));
                    application.setOfcl_GRE_subj(rs.getInt("ofcl_GRE_subj"));
                    application.setOfcl_GRE_subj_pctile(rs.getFloat("ofcl_GRE_subj_pctile"));
                    application.setOfcl_GRE_subj_name(rs.getString("ofcl_GRE_subj_name"));
                    application.setOfcl_TOEFL_total(rs.getInt("ofcl_TOEFL_total"));
                    application.setOfcl_TOEFL_listen(rs.getInt("ofcl_TOEFL_listen"));
                    application.setOfcl_TOEFL_read(rs.getInt("ofcl_TOEFL_read"));
                    application.setOfcl_TOEFL_speak(rs.getInt("ofcl_TOEFL_speak"));
                    application.setOfcl_TOEFL_write(rs.getInt("ofcl_TOEFL_write"));
                }

            }
            rs.close();
            s.close();
        } catch (Exception e) {
            System.out.println("Exception is ;" + e);
        }
    }
}
